--API Call by Hour with Avg. Time (Last 30 Days)--
SELECT SUBSTRING(WHENRECEIVED, 1, 10) as Date_Hour, COUNT(*) as Total, AVG(ELAPSEDTIME) as Avg_Elapsed_Time
FROM dsiTransactionLog
WHERE WHENRECEIVED between convert(varchar(25),DATEADD(DAY, -30, GETDATE()),112) ++ replace(convert(varchar(25), DATEADD(DAY, -1, GETDATE()), 108),':','')
AND convert(varchar(25),getdate(),112) ++ replace(convert(varchar(25), getdate(), 108),':','')
GROUP BY SUBSTRING(WHENRECEIVED, 1, 10)
ORDER BY SUBSTRING(WHENRECEIVED, 1, 10)

--API Call by Hour with Avg. Time (Last 24 Hours)--
SELECT SUBSTRING(WHENRECEIVED, 1, 10) as Date_Hour, COUNT(*) as Total, AVG(ELAPSEDTIME) as Avg_Elapsed_Time
FROM dsiTransactionLog
WHERE WHENRECEIVED between convert(varchar(25),DATEADD(DAY, -1, GETDATE()),112) ++ replace(convert(varchar(25), DATEADD(DAY, -1, GETDATE()), 108),':','')
AND convert(varchar(25),getdate(),112) ++ replace(convert(varchar(25), getdate(), 108),':','')
GROUP BY SUBSTRING(WHENRECEIVED, 1, 10)
ORDER BY SUBSTRING(WHENRECEIVED, 1, 10)